/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Gruppe 4
 */
public class DBSupplier implements IFDBSupplier{
    
    private  Connection con;
    PreparedStatement pStmtSelect;
    
    /** Creates a new instance 
     * of DBSupplier */
    public DBSupplier() {
      con = DbConnection1.getInstance().getDBcon();
    }
    
    // get all suppliers
    @Override
    public ArrayList<Supplier> getAllSuppliers(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    //get one supplier having the specified supplier id
    @Override
    public Supplier findSupplier(int supplierId, boolean retriveAssociation)
    {
        Supplier supObj = new Supplier();
        String wClause = "  supplierId = '" + supplierId + "'";
        supObj = singleWhere(wClause, retriveAssociation);
        //supObj = findWithPreQuery(supplierId);
        return supObj;
        
        //return singleWhere(wClause, retriveAssociation);
    }
    //find one supplier having the specified name
    @Override
    public Supplier searchSupplierName( String name, boolean retriveAssociation)
    {
        String wClause = "name like '%" + name + "%'";
        System.out.println("SearchSupplier " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
  
    //insert a new supplier
    @Override
    public int insertSupplier(Supplier sup)
    {
        //call to get the next supplier id
        int nextSupplierId = GetMax.getMaxId("Select max(supplierId) from suppliers");
        nextSupplierId = nextSupplierId + 1;
        System.out.println("next supplier id = " +  nextSupplierId);
  
       int rc = -1;
       
       if(!zipCodeExists(sup.getzipCode()))
       {
           System.out.println(zipCodeExists(sup.getzipCode()));
           System.out.println(sup.getzipCode());
	   int zipCode = sup.getzipCode();
           String city = sup.getCity();
           insertZipCode(zipCode, city);
                    
       }
      
           String query="INSERT INTO suppliers(name, address, zipCode, country, phoneNo, email, accountInfo, supplierId)  VALUES('"+
                        sup.getName()  + "','"  +
		    	sup.getAddress()  + "','"  +
                        sup.getzipCode() + "','"  +
                        sup.getCountry() + "','"  +
                        sup.getPhoneNo() + "','"  +
                        sup.getEmail() + "','"  +
                        sup.getAccountInfo() + "','" +
                        nextSupplierId + "')"; 
           
           System.out.println("insert : " + query);
           
           rc = updateQuery(rc, query);
       
	   return(rc);
    }
    
    //update information about a supplier
    @Override
    public int updateSupplier(Supplier sup)
    {
        Supplier supObj  = sup;
		int rc=-1;
	  
		String query="UPDATE suppliers SET "+
		 	  "name ='"+ supObj.getName()+"', "+
		 	  "phoneNo ='"+ supObj.getPhoneNo() + "' " +
		          " WHERE supplierId = '"+ supObj.getSupplierId() + "'";
                System.out.println("Update query:" + query);
  		
                rc= updateQuery(rc, query);
                
		return(rc);
    }
    
    public int delete(int supplierId)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM suppliers WHERE supplierId = '" +
				supplierId + "'";
                System.out.println(query);
	  	
                rc = updateQuery(rc, query);
                
		return(rc);
	}
    
    //Singelwhere is used when we only select one supplier 	
	private Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier supObj = new Supplier();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the supplier from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            supObj = buildSupplier(results);
                            //assocaition is to be build
                            //no associations
                            stmt.close();
                            if(retrieveAssociation)
                            {   
                            }
			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return supObj;
	}
        
        //michWere is used whenever we want to select more than one supplier
	
	 
	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Supplier> list = new ArrayList<Supplier>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the supplier from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
		while( results.next() ){
	     	 Supplier supObj = new Supplier();
		 supObj = buildSupplier(results);	
                 list.add(supObj);	
		}//end while
                 stmt.close();     
             
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
        
        //method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM suppliers";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
        
        //method to build an supplier object
	private Supplier buildSupplier(ResultSet results)
      {   Supplier supObj = new Supplier();
          try{
                supObj.setName(results.getString("name"));
                supObj.setAddress(results.getString("address"));
                supObj.setzipCode(results.getInt("zipCode"));
                supObj.setCountry(results.getString("country"));
	  	supObj.setPhoneNo(results.getString("phoneNo"));
	  	supObj.setEmail(results.getString("email"));
                supObj.setAccountInfo(results.getString("accountInfo"));
                supObj.setSupplierId(results.getInt("supplierId"));
                
          }
         catch(Exception e)
         {
             System.out.println("error in building the supplier object");
         }
         return supObj;
      }
        
      public boolean zipCodeExists(int zipCode)
      {
          String query="SELECT * FROM ZipCode";
          ResultSet results;
          int zip = 0;
          boolean found = false;
          try{ // read the zipcodes from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		while( results.next() ){
                            if(results.getInt("zipCode") == zipCode)
                            {
                                found = true;                           
                            }
                        }
                            stmt.close();
                            }//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
          return found;
      }
      
      public int insertZipCode(int zipCode, String city)
      {
          String query = "INSERT INTO ZipCode(zipCode, city)  VALUES('"+
                        zipCode  + "','"  +
                        city + "')";
          
          System.out.println("insert : " + query);
          
          int rc = -1;
          try{ // insert new zipcode
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in supplier db: "+ex);
	   }
	   return(rc);
      }
      
      private int updateQuery(int rc, String query)
    {
        try{ // insert new person
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in Suppliers db: "+ex);
	   }
        return rc;
    }
      
      public int getMaxId()
        {
            int maxOrderId = GetMax.getMaxId("Select max(supplierId) from Suppliers");
            return maxOrderId;
        }
      
      public Supplier findWithPreQuery(int supplierId)
        {   System.out.println("Find with preCompiled");
            if(pStmtSelect == null){
               pStmtSelect =  getPreparedStmt();          
            }
            Supplier supObj = null;
            ResultSet results;
            try{
           
            pStmtSelect.setInt(1,supplierId);
            results = pStmtSelect.executeQuery();
            if(results.next()){
                 supObj = buildSupplier(results);	
            }
            }
            catch(Exception e){
                e.getMessage();
            }
            return supObj;
       }
      
      private PreparedStatement getPreparedStmt()
        {
           try{ 
            String query = "SELECT * FROM Supplier" +
                           " WHERE supplierId = ?";
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
            
           }
           catch(Exception e){
                e.getMessage();
            }
           return pStmtSelect;
        }
}
